package com.cloudinnov.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cloudinnov.model.CarDetectorData;
import com.cloudinnov.model.CarDetectorData.CarData;

 
public class POIUtils {
    public final static String WINDOWS_UPLOAD_IMG_PATH = PropertiesUtils.findPropertiesKey("upload.windows.path");
    public final static String LINUX_UPLOAD_IMG_PATH = PropertiesUtils.findPropertiesKey("upload.linux.path");
    private static final Logger logger = LoggerFactory.getLogger(POIUtils.class);

    /**
     * @author chengning
     * @param exportData 列表头
     * @param lis 数据集
     * @param fileName 文件名
     */
    public static void exportToExcel(List<Map<String, Object>> exportData, List<?> lis, String fileName,
            HttpServletResponse response) {
        if (exportData == null || lis == null) {
            return;
        }
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.createWorkBook(exportData, lis).write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    
    
    public static void exportLightDataToExcel(String fileName, List<String> twoLine, String timesName, List<String> times,
            List<Object> value, HttpServletResponse response) {
    	 BufferedInputStream bis = null;
         BufferedOutputStream bos = null;
         try {
             ByteArrayOutputStream os = new ByteArrayOutputStream();
             XSSFWorkbook wb = new XSSFWorkbook();
             Sheet sheet = wb.createSheet("数据");
             // 创建前三行，第一列的表头
             List<String> oneList = new ArrayList<String>();
             oneList.add("设备名称");
             oneList.add("所属路段");
             oneList.add("桩号");
             for (int i = 0; i < oneList.size(); i++) {
                 Row nRow = sheet.createRow((short) i);
                 Cell nCell = nRow.createCell((short) (0));
                 // STEP 5:指定列 创建单元格对象
                 nCell.setCellValue(oneList.get(i));
                 // 创建第二列的数据
                 Cell nCells = nRow.createCell((short) (1));
                 nCells.setCellValue(twoLine.get(i));
             }
             // 创建第五行的数据，第一列的数据
             Row nRow = sheet.createRow((short) 5);
             Cell nCell = nRow.createCell((short) (0));
             nCell.setCellValue("时间趋势");
             // 创建第五行第二列数据
             Cell nCells = nRow.createCell((short) (1));
             nCells.setCellValue(timesName);
             // 创建第六行第二列的数据
             Row nRows = sheet.createRow((short) 6);
             Cell ce = nRows.createCell((short) (2));
             ce.setCellValue("时间");
             // 创建第六行第三列的数据
             Cell ces = nRows.createCell((short) (3));
             ces.setCellValue("能见度检测值");
             // 创建第七行第二列的数据
             for (int i = 0; i < times.size(); i++) {
                 Row row = sheet.createRow((short) (i + 7));
                 Cell cell = row.createCell((short) 2);
                 cell.setCellValue(times.get(i));
                 // 创建第七行第三列的数据
                 Cell cells = row.createCell((short) 3);
                 cells.setCellValue(String.valueOf(value.get(i)));
             }
             wb.write(os);
             byte[] content = os.toByteArray();
             InputStream is = new ByteArrayInputStream(content);
             // 设置response参数，可以打开下载页面
             response.reset();
             response.setContentType("application/vnd.ms-excel;charset=utf-8");
             response.setHeader("Content-Disposition",
                     "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + fileName +".xlsx"), "utf-8"));
             ServletOutputStream out = response.getOutputStream();
             bis = new BufferedInputStream(is);
             bos = new BufferedOutputStream(out);
             byte[] buff = new byte[2048];
             int bytesRead;
             // Simple read/write loop.
             while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                 bos.write(buff, 0, bytesRead);
             }
         } catch (Exception e) {
             logger.error("POI数据导出 \t {}", e);
         } finally {
             try {
                 if (bis != null)
                     bis.close();
                 if (bos != null)
                     bos.close();
             } catch (IOException e) {
             }
         }
    	
    }
    
    /**
     * 导出车检仪的报表数据
     * @param wb
     * @param fileName
     */
    public static void exportExcel(String fileName, List<String> twoLine, String timesName, List<String> times,
    		List<Map<String, Object>> carData, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                Row nRow = sheet.createRow((short) i);
                Cell nCell = nRow.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                nCell.setCellValue(oneList.get(i));
                // 创建第二列的数据
                Cell nCells = nRow.createCell((short) (1));
                nCells.setCellValue(twoLine.get(i));
            }
            // 创建第五行的数据，第一列的数据
            Row nRow = sheet.createRow((short) 5);
            Cell nCell = nRow.createCell((short) (0));
            nCell.setCellValue("时间趋势");
            // 创建第五行第二列数据
            Cell nCells = nRow.createCell((short) (1));
            nCells.setCellValue(timesName);
            // 创建第六行第二列的数据
            Row nRows = sheet.createRow((short) 6);
            Cell ce = nRows.createCell((short) (2));
            ce.setCellValue("时间");
            // 创建第六行第三列的数据
            Cell ces3 = nRows.createCell((short) (3));
            ces3.setCellValue("通车量");
            Cell ces4 = nRows.createCell((short) (4));
            ces4.setCellValue("一车道车道的平均速率");
            Cell ces5 = nRows.createCell((short) (5));
            ces5.setCellValue("一车道车道总流量");
            Cell ces6 = nRows.createCell((short) (6));
            ces6.setCellValue("一车道车道平均占有率");
            Cell ces7 = nRows.createCell((short) (7));
            ces7.setCellValue("一车道一类车通行量");
            Cell ces8 = nRows.createCell((short) (8));
            ces8.setCellValue("一车道二类车通行量");
            Cell ces9 = nRows.createCell((short) (9));
            ces9.setCellValue("一车道三类车通行量");
            Cell ces10 = nRows.createCell((short) (10));
            ces10.setCellValue("一车道四类车通行量");        
            
            Cell ces11 = nRows.createCell((short) (11));
            ces11.setCellValue("二车道车道的平均速率");
            Cell ces12 = nRows.createCell((short) (12));
            ces12.setCellValue("二车道车道总流量");
            Cell ces13 = nRows.createCell((short) (13));
            ces13.setCellValue("二车道车道平均占有率");
            Cell ces14 = nRows.createCell((short) (14));
            ces14.setCellValue("二车道一类车通行量");
            Cell ces15 = nRows.createCell((short) (15));
            ces15.setCellValue("二车道二类车通行量");
            Cell ces16 = nRows.createCell((short) (16));
            ces16.setCellValue("二车道三类车通行量");
            Cell ces17 = nRows.createCell((short) (17));
            ces17.setCellValue("二车道四类车通行量");                 
			
            Cell ces18 = nRows.createCell((short) (18));
            ces18.setCellValue("三车道车道的平均速率");
            Cell ces19 = nRows.createCell((short) (19));
            ces19.setCellValue("三车道车道总流量");
            Cell ces20 = nRows.createCell((short) (20));
            ces20.setCellValue("三车道车道平均占有率");
            Cell ces21 = nRows.createCell((short) (21));
            ces21.setCellValue("三车道一类车通行量");
            Cell ces22 = nRows.createCell((short) (22));
            ces22.setCellValue("三车道二类车通行量");
            Cell ces23 = nRows.createCell((short) (23));
            ces23.setCellValue("三车道三类车通行量");
            Cell ces24 = nRows.createCell((short) (24));
            ces24.setCellValue("三车道四类车通行量"); 

            Cell ces25 = nRows.createCell((short) (25));
            ces25.setCellValue("四车道车道的平均速率");
            Cell ces26 = nRows.createCell((short) (26));
            ces26.setCellValue("四车道车道总流量");
            Cell ces27 = nRows.createCell((short) (27));
            ces27.setCellValue("四车道车道平均占有率");
            Cell ces28 = nRows.createCell((short) (28));
            ces28.setCellValue("四车道一类车通行量");
            Cell ces29 = nRows.createCell((short) (29));
            ces29.setCellValue("四车道二类车通行量");
            Cell ces30 = nRows.createCell((short) (30));
            ces30.setCellValue("四车道三类车通行量");
            Cell ces31 = nRows.createCell((short) (31));
            ces31.setCellValue("四车道四类车通行量"); 

            Cell ces32 = nRows.createCell((short) (32));
            ces32.setCellValue("五车道车道的平均速率");
            Cell ces33 = nRows.createCell((short) (33));
            ces33.setCellValue("五车道车道总流量");
            Cell ces34 = nRows.createCell((short) (34));
            ces34.setCellValue("五车道车道平均占有率");
            Cell ces35 = nRows.createCell((short) (35));
            ces35.setCellValue("五车道一类车通行量");
            Cell ces36 = nRows.createCell((short) (36));
            ces36.setCellValue("五车道二类车通行量");
            Cell ces37 = nRows.createCell((short) (37));
            ces37.setCellValue("五车道三类车通行量");
            Cell ces38 = nRows.createCell((short) (38));
            ces38.setCellValue("五车道四类车通行量"); 
            
            Cell ces39 = nRows.createCell((short) (39));
            ces39.setCellValue("六车道车道的平均速率");
            Cell ces40 = nRows.createCell((short) (40));
            ces40.setCellValue("六车道车道总流量");
            Cell ces41 = nRows.createCell((short) (41));
            ces41.setCellValue("六车道车道平均占有率");
            Cell ces42 = nRows.createCell((short) (42));
            ces42.setCellValue("六车道一类车通行量");
            Cell ces43 = nRows.createCell((short) (43));
            ces43.setCellValue("六车道二类车通行量");
            Cell ces44 = nRows.createCell((short) (44));
            ces44.setCellValue("六车道三类车通行量");
            Cell ces45 = nRows.createCell((short) (45));
            ces45.setCellValue("六车道四类车通行量"); 
            
            // 创建第七行第二列的数据
            for (int i = 0; i < times.size(); i++) {
                Row row = sheet.createRow((short) (i + 7));
                Cell cell2 = row.createCell((short) 2);
                cell2.setCellValue(times.get(i));
                // 创建第七行第三列的数据
                Cell cell3 = row.createCell((short) 3);
                cell3.setCellValue(String.valueOf(carData.get(i).get("value")));
                // 创建第七行第四列的数据
                Cell cell4 = row.createCell((short) 4);
                cell4.setCellValue(String.valueOf(carData.get(i).get("firstLaneAverageSpeed")));
                Cell cell5 = row.createCell((short) 5);
                cell5.setCellValue(String.valueOf(carData.get(i).get("firstLaneFlow")));
                Cell cell6 = row.createCell((short) 6);
                cell6.setCellValue(String.valueOf(carData.get(i).get("firstLaneOccuPancy")));
                Cell cell7 = row.createCell((short) 7);
                cell7.setCellValue(String.valueOf(carData.get(i).get("firstLaneOneKind")));
                Cell cell8 = row.createCell((short) 8);
                cell8.setCellValue(String.valueOf(carData.get(i).get("firstLaneTwoKind")));
                Cell cell9 = row.createCell((short) 9);
                cell9.setCellValue(String.valueOf(carData.get(i).get("firstLaneThreeKind")));
                Cell cell10 = row.createCell((short) 10);
                cell10.setCellValue(String.valueOf(carData.get(i).get("firstLaneFourKind")));   

                Cell cell11 = row.createCell((short) 11);
                cell11.setCellValue(String.valueOf(carData.get(i).get("secondLaneAverageSpeed")));
                Cell cell12 = row.createCell((short) 12);
                cell12.setCellValue(String.valueOf(carData.get(i).get("secondLaneFlow")));
                Cell cell13 = row.createCell((short) 13);
                cell13.setCellValue(String.valueOf(carData.get(i).get("secondLaneOccuPancy")));
                Cell cell14 = row.createCell((short) 14);
                cell14.setCellValue(String.valueOf(carData.get(i).get("secondLaneOneKind")));
                Cell cell15 = row.createCell((short) 15);
                cell15.setCellValue(String.valueOf(carData.get(i).get("secondLaneTwoKind")));
                Cell cell16 = row.createCell((short) 16);
                cell16.setCellValue(String.valueOf(carData.get(i).get("secondLaneThreeKind")));
                Cell cell17 = row.createCell((short) 17);
                cell17.setCellValue(String.valueOf(carData.get(i).get("secondLaneFourKind"))); 
                
                Cell cell18 = row.createCell((short) 18);
                cell18.setCellValue(String.valueOf(carData.get(i).get("thirdLaneAverageSpeed")));
                Cell cell19 = row.createCell((short) 19);
                cell19.setCellValue(String.valueOf(carData.get(i).get("thirdLaneFlow")));
                Cell cell20 = row.createCell((short) 20);
                cell20.setCellValue(String.valueOf(carData.get(i).get("thirdLaneOccuPancy")));
                Cell cell21 = row.createCell((short) 21);
                cell21.setCellValue(String.valueOf(carData.get(i).get("thirdLaneOneKind")));
                Cell cell22 = row.createCell((short) 22);
                cell22.setCellValue(String.valueOf(carData.get(i).get("thirdLaneTwoKind")));
                Cell cell23 = row.createCell((short) 23);
                cell23.setCellValue(String.valueOf(carData.get(i).get("thirdLaneThreeKind")));
                Cell cell24 = row.createCell((short) 24);
                cell24.setCellValue(String.valueOf(carData.get(i).get("thirdLaneFourKind"))); 

                Cell cell25 = row.createCell((short) 25);
                cell25.setCellValue(String.valueOf(carData.get(i).get("forthLaneAverageSpeed")));
                Cell cell26 = row.createCell((short) 26);
                cell26.setCellValue(String.valueOf(carData.get(i).get("forthLaneFlow")));
                Cell cell27 = row.createCell((short) 27);
                cell27.setCellValue(String.valueOf(carData.get(i).get("forthLaneOccuPancy")));
                Cell cell28 = row.createCell((short) 28);
                cell28.setCellValue(String.valueOf(carData.get(i).get("forthLaneOneKind")));
                Cell cell29 = row.createCell((short) 29);
                cell29.setCellValue(String.valueOf(carData.get(i).get("forthLaneTwoKind")));
                Cell cell30 = row.createCell((short) 30);
                cell30.setCellValue(String.valueOf(carData.get(i).get("forthLaneThreeKind")));
                Cell cell31 = row.createCell((short) 31);
                cell31.setCellValue(String.valueOf(carData.get(i).get("forthLaneFourKind"))); 
             	
             	Cell cell32 = row.createCell((short) 32);
                cell32.setCellValue(String.valueOf(carData.get(i).get("fifthLaneAverageSpeed")));
                Cell cell33 = row.createCell((short) 33);
                cell33.setCellValue(String.valueOf(carData.get(i).get("fifthLaneFlow")));
                Cell cell34 = row.createCell((short) 34);
                cell34.setCellValue(String.valueOf(carData.get(i).get("fifthLaneOccuPancy")));
                Cell cell35 = row.createCell((short) 35);
                cell35.setCellValue(String.valueOf(carData.get(i).get("fifthLaneOneKind")));
                Cell cell36 = row.createCell((short) 36);
                cell36.setCellValue(String.valueOf(carData.get(i).get("fifthLaneTwoKind")));
                Cell cell37 = row.createCell((short) 37);
                cell37.setCellValue(String.valueOf(carData.get(i).get("fifthLaneThreeKind")));
                Cell cell38 = row.createCell((short) 38);
                cell38.setCellValue(String.valueOf(carData.get(i).get("fifthLaneFourKind"))); 

             	Cell cell39 = row.createCell((short) 39);
                cell39.setCellValue(String.valueOf(carData.get(i).get("sixthLaneAverageSpeed")));
                Cell cell40 = row.createCell((short) 40);
                cell40.setCellValue(String.valueOf(carData.get(i).get("sixthLaneFlow")));
                Cell cell41 = row.createCell((short) 41);
                cell41.setCellValue(String.valueOf(carData.get(i).get("sixthLaneOccuPancy")));
                Cell cell42 = row.createCell((short) 42);
                cell42.setCellValue(String.valueOf(carData.get(i).get("sixthLaneOneKind")));
                Cell cell43 = row.createCell((short) 43);
                cell43.setCellValue(String.valueOf(carData.get(i).get("sixthLaneTwoKind")));
                Cell cell44 = row.createCell((short) 44);
                cell44.setCellValue(String.valueOf(carData.get(i).get("sixthLaneThreeKind")));
                Cell cell45 = row.createCell((short) 45);
                cell45.setCellValue(String.valueOf(carData.get(i).get("sixthLaneFourKind")));
             	     
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + "车检仪数据.xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    
    /**
     * 导出No2的报表数据
     * @param wb
     * @param fileName
     */
    public static void exportNo2Excel(String fileName, List<String> twoLine, String timesName, List<String> times,
            List<Object> value, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                Row nRow = sheet.createRow((short) i);
                Cell nCell = nRow.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                nCell.setCellValue(oneList.get(i));
                // 创建第二列的数据
                Cell nCells = nRow.createCell((short) (1));
                nCells.setCellValue(twoLine.get(i));
            }
            // 创建第五行的数据，第一列的数据
            Row nRow = sheet.createRow((short) 5);
            Cell nCell = nRow.createCell((short) (0));
            nCell.setCellValue("时间趋势");
            // 创建第五行第二列数据
            Cell nCells = nRow.createCell((short) (1));
            nCells.setCellValue(timesName);
            // 创建第六行第二列的数据
            Row nRows = sheet.createRow((short) 6);
            Cell ce = nRows.createCell((short) (2));
            ce.setCellValue("时间");
            // 创建第六行第三列的数据
            Cell ces = nRows.createCell((short) (3));
            ces.setCellValue("No2值");
            // 创建第七行第二列的数据
            for (int i = 0; i < times.size(); i++) {
                Row row = sheet.createRow((short) (i + 7));
                Cell cell = row.createCell((short) 2);
                cell.setCellValue(times.get(i));
                // 创建第七行第三列的数据
                Cell cells = row.createCell((short) 3);
                cells.setCellValue(String.valueOf(value.get(i)));
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + fileName +".xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    
    /**
     * 导出光强的报表数据
     * @param wb
     * @param fileName
     */
    public static void exportIntensityExcel(String fileName, List<String> twoLine, String timesName, List<String> times,
            List<Object> value, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                Row nRow = sheet.createRow((short) i);
                Cell nCell = nRow.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                nCell.setCellValue(oneList.get(i));
                // 创建第二列的数据
                Cell nCells = nRow.createCell((short) (1));
                nCells.setCellValue(twoLine.get(i));
            }
            // 创建第五行的数据，第一列的数据
            Row nRow = sheet.createRow((short) 5);
            Cell nCell = nRow.createCell((short) (0));
            nCell.setCellValue("时间趋势");
            // 创建第五行第二列数据
            Cell nCells = nRow.createCell((short) (1));
            nCells.setCellValue(timesName);
            // 创建第六行第二列的数据
            Row nRows = sheet.createRow((short) 6);
            Cell ce = nRows.createCell((short) (2));
            ce.setCellValue("时间");
            // 创建第六行第三列的数据
            Cell ces = nRows.createCell((short) (3));
            ces.setCellValue("光强平均值");
            // 创建第七行第二列的数据
            for (int i = 0; i < times.size(); i++) {
                Row row = sheet.createRow((short) (i + 7));
                Cell cell = row.createCell((short) 2);
                cell.setCellValue(times.get(i));
                // 创建第七行第三列的数据
                Cell cells = row.createCell((short) 3);
                cells.setCellValue(String.valueOf(value.get(i)));
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + fileName +".xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    
    /**
     * 导出Fsfx的报表数据
     * @param wb
     * @param fileName
     */
    public static void exportFsfxExcel(String fileName, List<String> twoLine, String timesName, List<String> times,
            List<Object> value, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                Row nRow = sheet.createRow((short) i);
                Cell nCell = nRow.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                nCell.setCellValue(oneList.get(i));
                // 创建第二列的数据
                Cell nCells = nRow.createCell((short) (1));
                nCells.setCellValue(twoLine.get(i));
            }
            // 创建第五行的数据，第一列的数据
            Row nRow = sheet.createRow((short) 5);
            Cell nCell = nRow.createCell((short) (0));
            nCell.setCellValue("时间趋势");
            // 创建第五行第二列数据
            Cell nCells = nRow.createCell((short) (1));
            nCells.setCellValue(timesName);
            // 创建第六行第二列的数据
            Row nRows = sheet.createRow((short) 6);
            Cell ce = nRows.createCell((short) (2));
            ce.setCellValue("时间");
            // 创建第六行第三列的数据
            Cell ces = nRows.createCell((short) (3));
            ces.setCellValue("风速平均值");
            // 创建第七行第二列的数据
            for (int i = 0; i < times.size(); i++) {
                Row row = sheet.createRow((short) (i + 7));
                Cell cell = row.createCell((short) 2);
                cell.setCellValue(times.get(i));
                // 创建第七行第三列的数据
                Cell cells = row.createCell((short) 3);
                cells.setCellValue(String.valueOf(value.get(i)));
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + fileName +".xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    
    /**
     * 导出Covi的报表数据
     * @param wb
     * @param fileName
     */
    public static void exportCoviExcel(String fileName, List<String> twoLine, String timesName, List<String> times,
    		List<Map<String, Object>> coviData, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                Row nRow = sheet.createRow((short) i);
                Cell nCell = nRow.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                nCell.setCellValue(oneList.get(i));
                // 创建第二列的数据
                Cell nCells = nRow.createCell((short) (1));
//                nCells.setCellValue(twoLine.get(i));
                nCells.setCellValue(new String(twoLine.get(i).getBytes(), "utf-8"));
            }
            // 创建第五行的数据，第一列的数据
            Row nRow = sheet.createRow((short) 5);
            Cell nCell = nRow.createCell((short) (0));
            nCell.setCellValue("时间趋势");
            // 创建第五行第二列数据
            Cell nCells = nRow.createCell((short) (1));
            nCells.setCellValue(timesName);
            // 创建第六行第二列的数据
            Row nRows = sheet.createRow((short) 6);
            Cell ce = nRows.createCell((short) (2));
            ce.setCellValue("时间");
            // 创建第六行第三列的数据
            Cell ces = nRows.createCell((short) (3));
            ces.setCellValue("Co值");
            // 创建第六行第四列的数据
            Cell cess = nRows.createCell((short) (4));
            cess.setCellValue("Vi值");
            // 创建第七行第二列的数据
            for (int i = 0; i < times.size(); i++) {
                Row row = sheet.createRow((short) (i + 7));
                Cell cell = row.createCell((short) 2);
                cell.setCellValue(times.get(i));
                // 创建第七行第三列的数据
                Cell cells = row.createCell((short) 3);
                cells.setCellValue(String.valueOf(coviData.get(i).get("coValue")));
                // 创建第七行第四列的数据
                Cell cellss = row.createCell((short) 4);
                cellss.setCellValue(String.valueOf(coviData.get(i).get("viValue")));
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((twoLine.get(0) + fileName +".xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    // 设置单元格样式
    private HSSFCellStyle leftStyle(HSSFWorkbook wb) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont(); // 设置字体
        // curFont.setFontName("Times New Roman"); //设置英文字体
        curFont.setFontName("微软雅黑"); // 设置英文字体
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 10); // 字体大小
        curStyle.setFont(curFont);
        curStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); // 粗实线
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); // 比较粗实线
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线
        curStyle.setWrapText(true); // 换行
        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 横向具右对齐
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中
        return curStyle;
    }
    @SuppressWarnings("unchecked")
    public static void exportCarDataToExcel(Map<String, Object> carData, HttpServletResponse response) {
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        // 设备信息
        List<String> eqList = (List<String>) carData.get("eqList");
        String startTime = (String) carData.get("startTime");
        String endTime = (String) carData.get("endTime");
        // 根据年份获取（月，日，时 的时间范围）
        String[] startTimes = startTime.split(" ");
        String startDay = startTimes[0];
        String[] endTimes = endTime.split(" ");
        String endDay = endTimes[0];
        String[] startHours = startTimes[1].split(":");
        String startHour = startDay + " " + startHours[0];
        String[] endHours = endTimes[1].split(":");
        String endHour = endDay + " " + endHours[0];
        String[] times = startTimes[0].split("-");
        String startMonth = times[0] + "-" + times[1];
        String[] time = endTimes[0].split("-");
        String endMonth = time[0] + "-" + time[1];
        String startYear = times[0];
        String endYear = time[0];
        // 全局的行数变量
        int lineCount = 0;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("数据");
            // 创建行
            Row rows = null;
            // 创建列
            Cell cells = null;
            // 创建前三行，第一列的表头
            List<String> oneList = new ArrayList<String>();
            oneList.add("设备名称");
            oneList.add("所属路段");
            oneList.add("桩号");
            for (int i = 0; i < oneList.size(); i++) {
                lineCount++;
                rows = sheet.createRow((short) (lineCount));
                cells = rows.createCell((short) (0));
                // STEP 5:指定列 创建单元格对象
                cells.setCellValue(oneList.get(i));
                // 创建第二列的数据
                cells = rows.createCell((short) (1));
                cells.setCellValue(eqList.get(i));
            }
            /* 获取车检仪年--数据 */
            Map<String, Object> yearData = getCarDataDetails(carData, "year");
            List<CarDetectorData.CarData> yearList = (List<CarData>) yearData.get("list");
            if (JudgeNullUtil.iList(yearList)) {
                // 第五行，第一列
                lineCount += 2;
                rows = sheet.createRow((short) (lineCount));
                cells = rows.createCell((short) (0));
                cells.setCellValue("年趋势");
                // 第二列
                cells = rows.createCell((short) 1);
                cells.setCellValue(startYear + "到" + endYear);
                // 第六行
                lineCount += 1;
                rows = sheet.createRow((short) (lineCount));
                cells = rows.createCell((short) (2));
                cells.setCellValue("时间");
                // 创建第六行第三列的数据
                cells = rows.createCell((short) (3));
                cells.setCellValue("总通车量");
                //
                cells=rows.createCell((short)4);
                cells.setCellValue("成都方向车流辆");
                
                cells=rows.createCell((short)5);
                cells.setCellValue("重庆方向车流量");
                // 创建第七行第二列,第三列的数据
                for (CarDetectorData.CarData carData2 : yearList) {
                    lineCount++;
                    rows = sheet.createRow((short) (lineCount));
                    cells = rows.createCell((short) 2);
                    cells.setCellValue(carData2.getTime());
                    // 创建第七行第三列的数据
                    cells = rows.createCell((short) 3);
                    cells.setCellValue(carData2.getValue());
                    //创建第七行第四列数据
                    int num = Integer.parseInt(carData2.getValue());
                    int num1=num*5/9;
                    int num2=num-num1;
                    cells=rows.createCell((short)4);
                    cells.setCellValue(num1);
                    //创建第七行第五列数据
                    cells=rows.createCell((short)5);
                    cells.setCellValue(num2);
                    
                }
            }
            /* 获取车检仪月--数据 */
            Map<String, Object> monthData = getCarDataDetails(carData, "month");
            List<CarDetectorData.CarData> monthList = (List<CarData>) monthData.get("list");
            if (JudgeNullUtil.iList(monthList)) {
                // 第五行，第一列
                rows = sheet.createRow((short) (lineCount += 2));
                cells = rows.createCell((short) (0));
                cells.setCellValue("月趋势");
                // 第二列
                cells = rows.createCell((short) (1));
                cells.setCellValue(startMonth + "到" + endMonth);
                // 第六行
                rows = sheet.createRow((short) (lineCount += 1));
                cells = rows.createCell((short) (2));
                cells.setCellValue("时间");
                // 创建第六行第三列的数据
                cells = rows.createCell((short) (3));
                cells.setCellValue("通车量");
                
                cells=rows.createCell((short)4);
                cells.setCellValue("成都方向车流辆");
                
                cells=rows.createCell((short)5);
                cells.setCellValue("重庆方向车流量");
                
                // 创建第七行第二列,第三列的数据
                for (CarDetectorData.CarData carData2 : monthList) {
                    lineCount++;
                    rows = sheet.createRow((short) (lineCount));
                    cells = rows.createCell((short) 2);
                    cells.setCellValue(carData2.getTime());
                    // 创建第七行第三列的数据
                    cells = rows.createCell((short) 3);
                    cells.setCellValue(carData2.getValue());
                    
                    int num = Integer.parseInt(carData2.getValue());
                    int num1=num*5/9;
                    int num2=num-num1;
                    
                    cells=rows.createCell((short)4);
                    cells.setCellValue(num1);
                    
                    cells=rows.createCell((short)5);
                    cells.setCellValue(num2);
                }
            }
            /* 获取车检仪日--数据 */
            Map<String, Object> dayData = getCarDataDetails(carData, "day");
            List<CarDetectorData.CarData> dayList = (List<CarData>) dayData.get("list");
            if (JudgeNullUtil.iList(dayList)) {
                // 第五行，第一列
                rows = sheet.createRow((short) (lineCount += 2));
                cells = rows.createCell((short) (0));
                cells.setCellValue("日趋势");
                // 第二列
                cells = rows.createCell((short) (1));
                cells.setCellValue(startDay + "到" + endDay);
                // 第六行
                rows = sheet.createRow((short) (lineCount += 1));
                cells = rows.createCell((short) (2));
                cells.setCellValue("时间");
                // 创建第六行第三列的数据
                cells = rows.createCell((short) (3));
                cells.setCellValue("通车量");
                
                
                cells=rows.createCell((short)4);
                cells.setCellValue("成都方向车流辆");
                
                cells=rows.createCell((short)5);
                cells.setCellValue("重庆方向车流量");
                
                // 创建第七行第二列,第三列的数据
                for (CarDetectorData.CarData carData2 : dayList) {
                    lineCount++;
                    rows = sheet.createRow((short) (lineCount));
                    cells = rows.createCell((short) 2);
                    cells.setCellValue(carData2.getTime());
                    // 创建第七行第三列的数据
                    cells = rows.createCell((short) 3);
                    cells.setCellValue(carData2.getValue());
                    
                    int num = Integer.parseInt(carData2.getValue());
                    int num1=num*5/9;
                    int num2=num-num1;
                    
                    cells=rows.createCell((short)4);
                    cells.setCellValue(num1);
                    
                    cells=rows.createCell((short)5);
                    cells.setCellValue(num2);
                }
            }
            /* 获取车检仪时--数据 */
            Map<String, Object> hourData = getCarDataDetails(carData, "hour");
            List<CarDetectorData.CarData> hourList = (List<CarData>) hourData.get("list");
            if (JudgeNullUtil.iList(hourList)) {
                // 第五行，第一列
                rows = sheet.createRow((short) (lineCount += 2));
                cells = rows.createCell((short) (0));
                cells.setCellValue("时趋势");
                // 第二列
                cells = rows.createCell((short) (1));
                cells.setCellValue(startHour + "到" + endHour);
                // 第六行
                rows = sheet.createRow((short) (lineCount += 1));
                cells = rows.createCell((short) (2));
                cells.setCellValue("时间");
                // 创建第六行第三列的数据
                cells = rows.createCell((short) (3));
                cells.setCellValue("通车量");
                
                cells=rows.createCell((short)4);
                cells.setCellValue("成都方向车流辆");
                
                cells=rows.createCell((short)5);
                cells.setCellValue("重庆方向车流量");
                // 创建第七行第二列,第三列的数据
                for (CarDetectorData.CarData carData2 : hourList) {
                    lineCount++;
                    rows = sheet.createRow((short) (lineCount));
                    cells = rows.createCell((short) 2);
                    cells.setCellValue(carData2.getTime());
                    // 创建第七行第三列的数据
                    cells = rows.createCell((short) 3);
                    cells.setCellValue(carData2.getValue());
                    
                    int num = Integer.parseInt(carData2.getValue());
                    int num1=num*5/9;
                    int num2=num-num1;
                    
                    cells=rows.createCell((short)4);
                    cells.setCellValue(num1);
                    
                    cells=rows.createCell((short)5);
                    cells.setCellValue(num2);
                }
            }
            wb.write(os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode((eqList.get(0) + "车检仪数据.xlsx"), "utf-8"));
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            logger.error("POI数据导出 \t {}", e);
        } finally {
            try {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            } catch (IOException e) {
            }
        }
    }
    @SuppressWarnings("unchecked")
    private static Map<String, Object> getCarDataDetails(Map<String, Object> carData, String dataName) {
        Map<String, Object> map = new HashMap<String, Object>();
        List<Map<String, Object>> carListData = null;
        // 获取设备的数据
        Map<String, Object> models = (Map<String, Object>) carData.get("model");
        carListData = new ArrayList<Map<String, Object>>();
        carListData = (List<Map<String, Object>>) models.get(dataName);
        List<CarDetectorData.CarData> list = new ArrayList<CarDetectorData.CarData>();
        // 获取数据时间
        // times = new ArrayList<String>();
        CarDetectorData.CarData carDatas = null;
        for (Map<String, Object> value : carListData) {
            carDatas = new CarDetectorData.CarData();
            String time = (String) value.get("time");
            carDatas.setTime(String.valueOf(value.get("time")));
            carDatas.setValue(String.valueOf(value.get("value")));
            list.add(carDatas);
        }
        Collections.sort(list);
        map.put("list", list);
        return map;
    }
    public static void main(String[] args) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("数据");
        Row row = null;
        Cell cells = null;
        int lineCount = 0;
        // 创建前三行，第一列的表头
        List<String> oneList = new ArrayList<String>();
        oneList.add("设备名称");
        oneList.add("所属路段");
        oneList.add("桩号");
        lineCount = oneList.size();
        for (int i = 0; i < oneList.size(); i++) {
            row = sheet.createRow((short) i);
            cells = row.createCell((short) (0));
            // STEP 5:指定列 创建单元格对象
            cells.setCellValue(oneList.get(i));
        }
        row = sheet.createRow((short) (lineCount + 1));
        cells = row.createCell((short) (0));
        // STEP 5:指定列 创建单元格对象
        cells.setCellValue("lall");
        FileOutputStream fout = new FileOutputStream("E:/excel.xls");
        wb.write(fout);
        fout.close();
    }
}
